package shortURLDoubleCheck;

import java.io.BufferedReader;
import java.io.BufferedWriter;
import java.io.FileReader;
import java.io.FileWriter;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import Utility.Utility;
import extractURLsFromTweets.*;

public class shortURLDoubleCheck {
	private static Connection conn = null;
	private static int startingID = 0;
	private static int finalID = 0;
	private static int dbNum = 0;
	private static int BATCH_SIZE = 50;
	
	public static void init()
	{
		String db_name = "";		
		try
		{
			db_name = "twitter" + String.valueOf(dbNum) + ".db";
			Class.forName("org.sqlite.JDBC");
			conn = DriverManager.getConnection("jdbc:sqlite:"+db_name);
		}
		catch(SQLException e)
		{
			Utility.logMessage("ERROR: Could not open DB: " + db_name + "! " + e.getMessage(), "error_extractURLsFromTweets.txt");
			System.exit(1);
		}
		catch(ClassNotFoundException e)
		{
			Utility.logMessage("ERROR: " + e.getMessage(), "error_extractURLsFromTweets.txt");
			System.exit(1);
		}
		
		getStartingID();
		getFinalID();
	}
	
	public static void getFinalID()
	{
		Statement stmt = null;
		ResultSet rs = null;
		try
		{
			try
			{
				stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
			    rs = stmt.executeQuery("SELECT id FROM urls ORDER BY id DESC LIMIT 1;");
				rs.next();
				if (!rs.isAfterLast()) 
				{
					finalID = rs.getInt(1);
				}
			}
			catch(SQLException e)
			{
				Utility.logMessage("ERROR: " + e.getMessage(), "error_doubleCheck.txt");
			}
			finally
			{
				rs.close();
				stmt.close();
			}
		}
		catch(SQLException e)
		{
			Utility.logMessage("ERROR: " + e.getMessage(), "error_doubleCheck.txt");
		}
	}
	
	public static void getStartingID()
	{
		try
		{
			BufferedReader input =  new BufferedReader(new FileReader("doubleCheck.conf"));
			try 
			{
				startingID = Integer.valueOf(input.readLine());
				dbNum = Integer.valueOf(input.readLine());
			}
			
			finally {
				input.close();
			}
		}
		catch(IOException e)
		{
			Utility.logMessage("ERROR: Could not read in initial ID!", "error_doubleCheck.txt");
			System.exit(1);
		}
	}
	
	public static int doubleCheck(int current_id)
	{
		Statement stmt = null;
		ResultSet rs = null;
		String shorturl = null;
		String realurl = null;
		int lastID = 0;
		
		try
		{
			try
			{
				String instURLMap = "UPDATE urls SET url = ? WHERE url = ?;";				
				PreparedStatement psInstURLMap = conn.prepareStatement(instURLMap);
				
				stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
			    rs = stmt.executeQuery("SELECT url, id FROM urls WHERE id > " + current_id + " AND " +
			    		"url like '%%.ly/%%' OR url LIKE '%%tinyurl%%' LIMIT " + BATCH_SIZE + ";");
				rs.next();
				while (!rs.isAfterLast()) 
				{
					try
					{
						shorturl = rs.getString(1);
						lastID = rs.getInt(2);
						realurl = extractURLsFromTweets.getRealURL(shorturl);
						if(realurl.equals(shorturl))
						{
							psInstURLMap.setString(1, realurl);
							psInstURLMap.setString(2, shorturl);
							psInstURLMap.addBatch();
						}
					}
					catch(Exception e)
					{
						Utility.logMessage("Error converting " + shorturl + " " + e.getMessage(), "error_doubleCheck.txt");
					}
					rs.next();
				}
				psInstURLMap.executeBatch();
				psInstURLMap.clearBatch();
			}
			catch(SQLException e)
			{
				Utility.logMessage("ERROR: " + e.getMessage(), "error_doubleCheck.txt");
			}
			finally
			{
				rs.close();
				stmt.close();
			}
		}
		catch(SQLException e)
		{
			Utility.logMessage("ERROR: " + e.getMessage(), "error_doubleCheck.txt");
		}
		return lastID;
	}
	
	public static void saveStatus(int previous_id)
	{
		try{
			BufferedWriter f = new BufferedWriter(new FileWriter("doubleCheck.conf", false));
			f.write(previous_id + "\n");
			f.write(dbNum + "\n");
			f.close();
		}
		catch(IOException e)
		{
			e.printStackTrace();
		}
	}
	
	public static void main(String[] args)
	{
		init();
		int current_id = startingID;
		while(current_id < finalID)
		{
			current_id = doubleCheck(current_id);
			saveStatus(current_id);
		}
	}
}
